--Create Tables
USE ReportDB; 
GO 

DROP TABLE IF EXISTS JSONData

CREATE TABLE [dbo].[JSONData](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ReportID]  AS (json_value([DataFile],'$.StateCaseNumber')),
	[DataFile] [nvarchar](max) NULL,
	[LoadDate] [datetime] NOT NULL,
 CONSTRAINT [pk_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[JSONData] ADD  CONSTRAINT [DF_JSONData_LoadDate]  DEFAULT (getdate()) FOR [LoadDate]
GO

DROP TABLE IF EXISTS Crash

CREATE TABLE Crash
(
StateCaseNumber     nvarchar(20)  NOT NULL,
CrashDate Date,
OfficerName nvarchar(200),
Parish nvarchar(100),
City nvarchar(100),
FirstHarmfulEvent nvarchar(200),
CrashManner nvarchar(200),
RoadwaySurfaceCondition nvarchar(200),
ModifiedDate DateTime,
CONSTRAINT pk_StateCaseNumber PRIMARY KEY(StateCaseNumber)
);

DROP TABLE IF EXISTS Vehicle

CREATE TABLE Vehicle
(
StateCaseNumber nvarchar(20) NOT NULL,
VehicleNumber int NOT NULL,
VIN nvarchar(20),
LicensePlate nvarchar(20),
LicensePlateYear nvarchar(4),
Make nvarchar(100),
BodyType nvarchar(100),
ModifiedDate DateTime,
CONSTRAINT pk_State_Vehcile PRIMARY KEY(StateCaseNumber, VehicleNumber)
);

DROP TABLE IF EXISTS Driver

CREATE TABLE Driver
(
StateCaseNumber nvarchar(20) NOT NULL,
VehicleNumber int NOT NULL,
DriverNumber int NOT NULL,
VIN nvarchar(20),
LicenseStatus nvarchar(20),
LicenseState nvarchar(50),
AlcoholInterlockPresence nvarchar(20),
DrugTestStatus nvarchar(20),
ModifiedDate DateTime,
CONSTRAINT pk_State_Driver PRIMARY KEY(StateCaseNumber, VehicleNumber, DriverNumber)
);


DROP TABLE IF EXISTS BatchLog

CREATE TABLE [dbo].[BatchLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LoadDate] [datetime] NOT NULL,
	[RowVersion] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_BatchLog] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BatchLog] ADD  CONSTRAINT [DF_BatchLog_LoadDate]  DEFAULT (getdate()) FOR [LoadDate]
GO